import xlrd
import pymysql

con = pymysql.connect(host='localhost', user='root', password='123456', database='1_test', port=3306,
                      charset='utf8')
cursor = con.cursor()
book = xlrd.open_workbook(r'C:\Users\lenovo\Desktop\工作簿.xls')
sheet = book.sheet_by_name('Sheet1')
for r in range(1, sheet.nrows):
    data = [sheet.cell(r, col).value for col in range(sheet.ncols)]
    print(data)
try:
    with con.cursor() as cursor:
        for sheet in book:
            for r in range(1, sheet.nrows):
                sql = 'insert into net(yidong,liantong,dianxin) values (%s,%s,%s) '
                a1 = sheet.cell(r, 0).value
                a2 = sheet.cell(r, 1).value
                a3 = sheet.cell(r, 2).value
                values = (a1, a2, a3)
                cursor.execute(sql, values)
        con.commit()
finally:
    cursor.close()
    con.close()
print("存储成功！")
